package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.CrowdsourceTradeRecordDto;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceTradeRecord;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 智众包项目批量代付记录 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-09-25
 */
public interface CrowdsourceTradeRecordMapper extends BaseMapper<CrowdsourceTradeRecord> {

	@Select(" select batch_id " +
			" from zdd_crowdsource_trade_record tr " +
			" where tr.batch_status not in(4,7)")
	List<Integer> getInProcessTradeids();

	@Select(" SELECT batch_id " +
			" FROM zdd_crowdsource_trade_record tr " +
			" JOIN zdd_payment pay on tr.pay_id = pay.id " +
			" where tr.batch_status in(4,7)" +
			" AND pay.source_table = 1" +
			" AND tr.createtime >= DATE_SUB(NOW(),INTERVAL 2 DAY) ")
	List<Integer> getSuccessTradeIds();

	@Select(" select tr.*,c.compname,csp.project_name,csp.project_total_amount,csp.project_total_service_charge, " +
			" csp.project_total_payable_amount,csp.state,csp.party_id,csp.actual_sender,csp.supplement,csp.work_type,pay.pay_name," +
			" csp.tax_source_id " +
			" from zdd_crowdsource_trade_record tr " +
			" join zdd_company c on c.compid = tr.compid " +
			" left join zdd_payment pay on pay.id = tr.pay_id " +
			" join zdd_crowdsource_project csp on csp.projectid = tr.projectid " +
			" where if(ISNULL(#{state}),1=1,csp.state=#{state}) " +
			" and tr.createtime between #{startDate} and #{endDate} " +
			" and c.compname like '%${name}%' " +
			" and tr.deleted = 0 " +
			" order by csp.state ,csp.createtime DESC ")
	List<CrowdsourceTradeRecordDto> getTradeRecordList(@Param("name") String name, @Param("state") Integer state,
	                                                   @Param("startDate") Date startDate, @Param("endDate") Date endDate);


	@Select(" select tr.*,c.compname,csp.project_name,csp.project_total_amount,csp.project_total_service_charge, " +
			" csp.project_total_payable_amount,csp.state,pay.pay_name " +
			" from zdd_crowdsource_trade_record tr " +
			" join zdd_company c on c.compid = tr.compid " +
			" left join zdd_payment pay on pay.id = tr.pay_id " +
			" join zdd_crowdsource_project csp on csp.projectid = tr.projectid " +
			" where tr.compid = #{compid} " +
			" and if(ISNULL(#{state}),1=1,csp.state=#{state}) " +
			" and tr.createtime between #{startDate} and #{endDate} " +
			" and tr.deleted = 0 " +
			" order by tr.batch_id DESC ")
	List<CrowdsourceTradeRecordDto> getTradeRecordListByCompid(@Param("compid") int compid, @Param("state") Integer state,
	                                                   @Param("startDate") Date startDate, @Param("endDate") Date endDate);

	/**
	 * 查询创客和该企业在 智众包项目中 当前月 当前年 的项目金额
	 * @param compid 企业id
	 * @param userid 创客userid
	 * @param type 1 当月 2 当年  其他 全部
	 * @return
	 */
	@Select(" SELECT IFNULL( SUM( cpi.project_amount ), 0 ) FROM zdd_crowdsource_project_item cpi " +
			" LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = cpi.projectid " +
			" WHERE cp.compid = #{compid} AND cpi.user_type = 2 " +
			" AND cpi.userid = #{userid} AND cp.state IN ( 2, 3, 4 ) " +
			" and if(#{type} = 1 ,MONTH ( CURRENT_TIMESTAMP ( ) ) = MONTH ( cp.createtime )  AND YEAR ( CURRENT_TIMESTAMP ( ) ) = YEAR ( cp.createtime ), 1 = 1)  " +
			" and if(#{type} = 2 ,YEAR ( CURRENT_TIMESTAMP ( ) ) = YEAR ( cp.createtime ), 1 = 1) " )
	BigDecimal getCrowdSourceProjectAmountByUserid(@Param("compid") int compid,@Param("userid") int userid,@Param("type") int type);

	/**
	 * 创客和该企业 累计众包项目笔数
	 * @param compid 企业id
	 * @param userid 创客userid
	 * @return count
	 */
	@Select(" SELECT count( * ) FROM zdd_crowdsource_project_item cpi " +
			" LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = cpi.projectid " +
			" WHERE cp.compid = #{compid} AND cpi.user_type = 2 " +
			" AND cpi.userid = #{userid} AND cp.state IN ( 2, 3, 4 ) ")
	int getCrowdSourceProjectByUseridAndCompidCount(@Param("compid") int compid,@Param("userid") int userid);


	/**
	 * 查询创客在 智众包项目中 当前年 和 全部 的项目金额
	 * @param userid 创客userid
	 * @param type 1 当年  其他 全部
	 * @return
	 */
	@Select(" SELECT IFNULL( SUM( cpi.project_amount ), 0 ) FROM zdd_crowdsource_project_item cpi " +
			" LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = cpi.projectid " +
			" WHERE cpi.user_type = 2 " +
			" AND cpi.userid = #{userid} AND cp.state IN ( 2, 3, 4 ) " +
			" and if(#{type} = 1 ,YEAR ( CURRENT_TIMESTAMP ( ) ) = YEAR ( cp.createtime ), 1 = 1) " )
	BigDecimal getCrowdSourceProjectAmountByUseridAllCompany(@Param("userid") int userid,@Param("type") int type);

	@Select(" select tr.*,c.compname,csp.project_name,csp.project_total_amount,csp.project_total_service_charge, " +
			" csp.project_total_payable_amount,csp.state,csp.party_id,csp.actual_sender " +
			" from zdd_crowdsource_trade_record tr " +
			" join zdd_company c on c.compid = tr.compid " +
			" join zdd_crowdsource_project csp on csp.projectid = tr.projectid " +
			" where csp.state in (3,98,2)" +
			" and tr.deleted = 0 " +
			" order by tr.batch_id DESC ")
	List<CrowdsourceTradeRecordDto> statisticsTradeRecordList();

	@Select(" select SUM(csp.project_total_amount) as projectTotalAmount," +
			" SUM(csp.project_total_service_charge) as projectTotalServiceCharge, " +
			" SUM(csp.project_total_payable_amount) as projectTotalPayableAmount " +
			" from zdd_crowdsource_trade_record tr " +
			" join zdd_company c on c.compid = tr.compid " +
			" join zdd_crowdsource_project csp on csp.projectid = tr.projectid " +
			" where tr.compid = #{compid} " +
			" and if(ISNULL(#{state}),1=1,csp.state=#{state}) " +
			" and tr.createtime between #{startDate} and #{endDate} " +
			" and tr.deleted = 0 " )
	CrowdsourceTradeRecordDto transactionFlowStatistics(@Param("compid") int compid, @Param("state") Integer state,
															  @Param("startDate") Date startDate, @Param("endDate") Date endDate);

}
